package site.assad.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.util.FileCopyUtils;
import site.assad.domain.Game;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Author: Al-assad 余林颖
 * E-mail: yulinying_1994@outlook.com
 * Date: 2017/12/14 19:14
 * Description:
 */
@Repository
public class GameDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private LobHandler lobHandler;

    //演示对于 LOB 类型数据的插入
    public void addGame(final Game game){
        final String sql = "INSERT INTO games(game_name,game_description,game_thumbnail) " +
                "VALUES(?,?,?)";
        jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
            @Override
            protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException {
                ps.setString(1,game.getName());
                lobCreator.setClobAsString(ps,2,game.getDescription());  //设置 CLOB 字段
                lobCreator.setBlobAsBytes(ps,3,game.getThumbnail());   //设置 BLOB 字段
            }
        });
    }

    //演示对于 LOB 类型数据的读取
    //以块数据方式读取 LOB
    public Game getGameByName(final String gamename){
        final String sql = "SELECT game_id,game_name,game_description,game_thumbnail " +
                "FROM games " +
                "WHERE game_name = ?";
        Game game = new Game();
        jdbcTemplate.query(sql, new Object[]{gamename}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                game.setId(rs.getInt("game_id"));
                String description = lobHandler.getClobAsString(rs,3);
                byte[] thumbnail = lobHandler.getBlobAsBytes(rs,4);
                game.setDescription(description);
                game.setThumbnail(thumbnail);
            }
        });
        return game;
    }

    //以流数据方式读取 LOB
    public void getGameThumbnailByName(final String gamename,final OutputStream out){
        final String sql = "SELECT game_thumbnail " +
                "FROM games " +
                "WHERE game_name = ?";
        Game game = new Game();
        jdbcTemplate.query(sql, new Object[]{gamename},
                new AbstractLobStreamingResultSetExtractor<Game>() {
                    @Override
                    protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
                        InputStream in = lobHandler.getBlobAsBinaryStream(rs,1);
                        if(in != null)
                            FileCopyUtils.copy(in,out);
                    }
                });
    }

}
